from datetime import datetime,timezone, timedelta
from io import BytesIO
import io
import os
import uuid
from django.conf import settings
from django.http import HttpResponse
from django.shortcuts import render
from django.views.decorators.csrf import csrf_exempt
from django.views.decorators.http import require_http_methods
import redis

from yida_app_service.models import HzfwQrCode
from yida_app_service.shujian.xm_cloud_config import XmCloudClient as ShuJianCloudClient
from .db_sqlserver_utils import ShujianDBQueryTool
from yida_app_service.utils.hzfw_utils import gen_qr_code, update_report_info
from reportservice.helpers import ApiResponse
import json
from .yida_config import YidaClient
import requests
import qrcode
from reportlab.pdfgen import canvas
from PyPDF2 import PdfWriter, PdfReader
from reportlab.lib.utils import ImageReader
from urllib.parse import unquote
from oss_service.views import bucket
import pandas as pd
from io import BytesIO
from datetime import datetime
import pytz
yida_client = YidaClient()
xm_client = ShuJianCloudClient()
org_id = "956C7101F70743E29D252C9AB0C8D128"
admin_user_id = "301546025724261514"
r = redis.StrictRedis(host='10.8.0.83', port=6379, db=0)

@csrf_exempt
@require_http_methods(["POST"])
def apply_hzfw_qr(request):
    datasource: dict[str, any] = json.loads(request.body)
    report_code = datasource["reportCode"]
    file_url = datasource["fileUrl"]
    formUUID = datasource["formUUID"]
    default_position = [{
                          "page":1,
                          "posx":50,
                          "posy":50,
                          "size":100
                      }]
    positions = datasource.get("position",default_position)
    url_prefix = "https://api.zjcxjc.cn/api/v1/yida-app/common/download_file/shujian/reports/"
    full_download_url = yida_client.get_full_download_url(
        user_id=admin_user_id, download_url=file_url)
    response = requests.get(full_download_url.body.result)
    if response.status_code == 200:
        pdf_content = BytesIO(response.content)
        qr_code = HzfwQrCode.objects.filter(report_code=report_code, org_id=org_id, is_uploaded=False).order_by('-id').first()
        if not qr_code:
            try:
                res = gen_qr_code(org_id, report_code)
            except Exception as e:
                return ApiResponse.fail(message=f"生成二维码失败,{e}")
            if res["code"] == "ok":
                qr_data = res["qrcodestr"]
                qr_code = HzfwQrCode(report_code=report_code,
                                      org_id=org_id,
                                      company_name="shujian",
                                      qr_code_content=qr_data)
                qr_code.save()
        qr_data = qr_code.qr_code_content
        reprt_pdf = PdfReader(pdf_content)
        output = PdfWriter()
        for position in positions:
            page_num = int(position["page"])
            qr_x = int(position["posx"])
            qr_y = int(position["posy"])
            qr_size = int(position["size"])
            qr = qrcode.QRCode(
                version=1,
                error_correction=qrcode.constants.ERROR_CORRECT_L,
                box_size=10,
                border=4,
            )
            qr.add_data(qr_data)
            qr.make(fit=True)
            img = qr.make_image(fill_color="black", back_color="white")
            qr_io = BytesIO()
            img.save(qr_io, format="PNG")
            qr_io.seek(0)  # 移动到流的开头
            if page_num - 1 < len(reprt_pdf.pages):
                # 绘制 QR 码至指定页面
                packet = BytesIO()
                c = canvas.Canvas(packet)
                qr_image = ImageReader(qr_io)
                c.drawImage(qr_image, qr_x, qr_y, qr_size, qr_size)
                c.save()

                # 合并 QR 码页面到原始 PDF 页面
                packet.seek(0)
                qr_pdf = PdfReader(packet)
                reprt_pdf.pages[page_num - 1].merge_page(qr_pdf.pages[0])
        for page in reprt_pdf.pages:
            output.add_page(page)
        #pdf_path = f"shujian/{uuid.uuid4()}.pdf"
        UUID = uuid.uuid4()
        pdf_path = f'shujian/reports/{UUID}.pdf'
        tmp_path = f"{os.path.join(settings.BASE_DIR, r'data')}/xiaming_tmp/{pdf_path}"
        if not os.path.exists(os.path.dirname(tmp_path)):
            os.makedirs(os.path.dirname(tmp_path))
        with open(tmp_path, "wb") as outputStream:
            output.write(outputStream)
        bucket.put_object_from_file(pdf_path, tmp_path)
        qr_code.pdf_path = pdf_path
        qr_code.save()
        sign_url = bucket.sign_url('GET', pdf_path, 3600)

        update_form_data = {
                        "attachmentField_lti99yal": [{
                            "downloadUrl": f'{url_prefix}{UUID}.pdf',
                            "name": f'{report_code}01.pdf',
                            "previewUrl":  f'{url_prefix}{UUID}.pdf',
                            "url":  f'{url_prefix}{UUID}.pdf',
                            "ext": "pdf"
                          }],
                            "selectField_ltirw8ui":"已上传"
                          }   

        yida_client.update_form_data_by_field(
            form_uuid="FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2",
            userId=admin_user_id,
            search_field={"textField_lutjluha": formUUID},
            update_field=update_form_data,
        )
        return ApiResponse.success(sign_url)
    else:
        return ApiResponse.fail(res["msg"])

def date_format(value,default_str=None):
    try:
        date = datetime.fromtimestamp(value / 1000, timezone.utc)
        date += timedelta(hours=8)
        return date.strftime('%Y-%m-%d')
    except ValueError:
        return default_str

@csrf_exempt
@require_http_methods(["POST"])
def upload_hzfw_data(request):
    datasource = json.loads(request.body)
    try:
        area_name = "市辖区"
        form_inst_id = datasource["form_inst_id"]
        form_data = yida_client.get_form_items_by_ids("FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2",form_inst_id,admin_user_id)
        if form_data:
            form_data = form_data[0].form_data
        else:
            return ApiResponse.fail("未找到记录",400)
        report_code = form_data.get("textField_lqqbd27b")
        if not report_code:
            return ApiResponse.fail("未找到报告编号",400)
        report_info = {}
        report_info["GCMC"] = "-"
        try:
            project_str = form_data.get("associationFormField_lqqbd27d_id")
            project = json.loads(json.loads(project_str))
            report_info["GCMC"] = project[0].get("title","-") if project else "-"
        except:
            return ApiResponse.fail("工程名称载入失败",400)
        report_info["YPBH"] = form_data.get("textField_lqqbd27a","-")
        report_info["WTDW"] = form_data.get("textField_lqqbd27k","-")
        report_info["JCJL"] = form_data.get("textareaField_lqqbd290","-")
        report_info["BGRQ"] = date_format(form_data.get("dateField_lqqbd29j","-"),"-")
        report_info["JCRQ"] = date_format(form_data.get("dateField_lqqbd29e","-"),None)
        if not report_info["JCRQ"]:
            return ApiResponse.fail("检测日期错误",400)
        if form_data.get("dateField_lqqbd29f"):
            report_info["JCRQ2"] = date_format(form_data.get("dateField_lqqbd29f"))
        params = form_data.get('tableField_ltwahb9f')
        if params:
            try:
                report_info["JCNR"] = ";".join([item.get("selectField_ltwahb9g_id") for item in params])
            except:
                report_info["JCNR"] = "-"
        else:
            report_info["JCNR"] = "-"

        res = update_report_info(org_id,"",area_name,report_code,report_info)
        if "上传成功" in res:
            qr_code = HzfwQrCode.objects.filter(report_code=report_code, org_id=org_id, is_uploaded=False).order_by('-id').first()
            qr_code.is_uploaded = True
            qr_code.save()
            yida_client.update_form_data_by_field("FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2",admin_user_id,{"textField_lqqbd27b":report_code},{"selectField_ltirw8ui":"已上传"})
            return ApiResponse.success(res)
        else:
            return ApiResponse.fail(res,400)
    except KeyError:
        return ApiResponse.fail("字段必传校验失败",400)


@csrf_exempt
@require_http_methods(["GET"])
def upload_price_shell(request):
    # 从 GET 请求参数中获取下载链接，并解码该链接。unquote 用于处理 URL 编码。
    download_url = unquote(request.GET.get('download_url'))
    project_id = request.GET.get('project_id')
    full_download_url = yida_client.get_full_download_url(
        user_id=admin_user_id, download_url=download_url)

    url = full_download_url.body.result  # 从完整的下载链接响应中提取实际的文件下载链接。
    with requests.Session() as session:  # 使用 requests 库创建一个会话对象，用于发送 HTTP 请求。
        response = session.get(url)  # 发送 GET 请求以获取文件内容。
        if response.status_code == 200:
            file_content = response.content  # 从响应中获取文件的内容。
            bucket.put_object(
                rf"shujian/price_sheet/{project_id}/price_shell.xlsx", file_content)
            return ApiResponse.success()
    return ApiResponse.fail("清单上传失败")


@csrf_exempt
@require_http_methods(["GET"])
def prepare_price_shell(request):
    project_id = request.GET.get('project_id')
    price_shell_path = f"shujian/price_sheet/{project_id}/price_shell.xlsx"
    exist = bucket.object_exists(price_shell_path)
    if (exist):
        price_shell_content = bucket.get_object(price_shell_path).read()
        excel_file = BytesIO(price_shell_content)
        df = pd.read_excel(excel_file)
        for index, row in df.iterrows():
            redis_key = f"{str(row['检测项目'])}-{str(row['检测参数'])}"
            redis_value = str(row['费用'])
            # 存储到Redis
            r.set(f"shujian:{project_id}:{redis_key}", redis_value)
        return ApiResponse.success("success")
    else:
        return ApiResponse.fail("未找到价格清单")


@csrf_exempt
@require_http_methods(["GET"])
def get_single_price(request):
    project_id = request.GET.get('project_id')
    param_name = request.GET.get('param_name')
    param_key = rf"shujian:{project_id}:{param_name}"
    price = r.get(param_key)
    if price:
        price = int(price.decode())
        return ApiResponse.success(price)
    else:
        return ApiResponse.fail("未找到价格")


@csrf_exempt
@require_http_methods(["POST"])
def gen_total_price(request):
    datasource: dict[str, any] = json.loads(request.body)
    project_id = datasource["project_id"]
    param_names = datasource['param_names']
    res_price = 0
    for param_name in param_names:
        param_key = rf"shujian:{project_id}:{param_name}"
        price = r.get(param_key)
        if price:
            price = int(price.decode())
            res_price = res_price + price
        else:
            ApiResponse.fail("未找到价格")
    return ApiResponse.success(res_price)


@csrf_exempt
@require_http_methods(["GET"])
def get_hnjy_data(request):
    is_async = int(request.GET.get('is_async',-1))
    page_size = request.GET.get('pageSize', '10')
    page_size = int(page_size)
    page = request.GET.get('currentPage', '1')
    page = int(page)
    ProjectName = request.GET.get('ProjectName', '')
    ProjectNum = request.GET.get('ProjectNum', '')
    # 假设您从请求中获取到的时间戳是字符串形式
    timestamp_str_start = request.GET.get('EntrustDate[start]', '1046684800000')
    timestamp_str_end = request.GET.get('EntrustDate[end]', '4070908800000')

    # 将字符串转换为整数
    timestamp_int_start = int(timestamp_str_start[:10])
    timestamp_int_end = int(timestamp_str_end[:10])

    # 将时间戳转换为 datetime 对象
    start_EntrustDate = datetime.fromtimestamp(timestamp_int_start)
    end_EntrustDate = datetime.fromtimestamp(timestamp_int_end)
    # 计算 OFFSET 值
    offset = (page - 1) * page_size
    async_param = ''
    if is_async == -1:
        async_param = 'IS NULL OR a.is_async = 1'
    elif is_async == 0:
        async_param = 'IS NULL'
    elif is_async == 1:
        async_param = '= 1'
    # 构建查询语句，包括分页和模糊查询
    query = f"""
        SELECT ReportNum, SampleNum, EntrustNum, b.Name AS TestItemName, TestItemParmName, ProjectNum, ProjectName, EntrustUnitName, SendSampleMan, EntrustDate, Sizes, Conclusion, NeedChargeMoney,SysPrimaryKey,is_async
        FROM EntrustBills a
        JOIN testitems b ON a.Code = b.Code
        WHERE a.EntrustDate BETWEEN %s AND %s
        AND a.ProjectName LIKE %s
        AND a.ProjectNum LIKE %s
        AND (a.is_async {async_param})
        ORDER BY a.SysPrimaryKey  -- 假设根据报告编号排序，您可以根据需要调整
        OFFSET %s ROWS
        FETCH NEXT %s ROWS ONLY
    """
    # 构建用于获取总记录数的查询语句
    query_total = f"""
        SELECT COUNT(*)
        FROM EntrustBills a
        JOIN testitems b ON a.Code = b.Code
        WHERE a.EntrustDate BETWEEN %s AND %s
        AND a.ProjectName LIKE %s
        AND a.ProjectNum LIKE %s
        AND (a.is_async {async_param})
    """
    params = (start_EntrustDate, end_EntrustDate, f"%{ProjectName}%", f"%{ProjectNum}%", offset, page_size)
    params_total = (start_EntrustDate, end_EntrustDate, f"%{ProjectName}%", f"%{ProjectNum}%")
    # 执行查询
    db = ShujianDBQueryTool()
    rows = db.execute_query(query, params)
    total = db.execute_query(query_total, params_total)[0][0]  # 总数将是第一个查询返回的第一个字段
    entrust_bills = []
    for row in rows:
        entrust_bills.append(
          {
            "ReportNum":row[0],
            "SampleNum":row[1],
            "EntrustNum":row[2],
            "TestItemName":row[3],
            "TestItemParmName":row[4],
            "ProjectNum":row[5],
            "ProjectName":row[6],
            "EntrustUnitName":row[7],
            "SendSampleMan":row[8],
            "EntrustDate":row[9],
            "Sizes":row[10],
            "Conclusion":row[11],
            "NeedChargeMoney":row[12],
            "SysPrimaryKey":row[13],
            "is_async":row[14]
          }
        )

    return ApiResponse.success({
        'data': entrust_bills,
        'total':total,
        "currentPage":page
    })

#手动同步    
@csrf_exempt
@require_http_methods(["POST"])
def async_hnjy_data(request):
    data = json.loads(request.body)
    entrust_bills = data.get("entrust_bills", [])

    db = ShujianDBQueryTool()
    row_count=0

    for row in entrust_bills:
        # 检查并新增钉钉工程管理表中的工程
        check_and_create_project(row=row,form_uuid="FORM-CEEA0B17FCCD4D6285FF6E7239B2D85A4R2I")


         # 处理 TestItemParm 数组转换为 tableField_ltwahb9f 所需格式
        tableField_ltwahb9f = [
            {
                "selectField_ltwahb9g": item.get("TestItemParmName"),  # 检测参数
                "numberField_ltwahb9j": item.get("NeedChargeMoney")    # 单组费用
            }
            for item in row.get("TestItemParm", [])  # 确保 TestItemParm 存在，否则默认为空列表
        ]


        formDataJson={
            "selectField_ltzweoh3": "同步", # 来源
            "textField_lqqbd279": row["EntrustNum"],# 委托编号
            "textField_lqqbd27a": row["SampleNum"],#  样品编号
            "textField_lqqbd27b": row["ReportNum"],#  报告编号
            "textField_lus52pqi":row["ProjectName"],# _工程名称
            "selectField_lrpzpf08": row["TestItemName"],#  检测项目
            "tableField_ltwahb9f": tableField_ltwahb9f,#  检测参数
            "textField_lqqbd27e": row["ProjectNum"],#  工程编号
            "textField_luow218y": row["ProjectNum"],#  _工程编号(搜索)
            "textField_lqqbd27k": row["EntrustUnitName"],#  委托单位
            "textField_logmivk6": row["SendSampleMan"],#  送样人
            "dateField_lqqbd288": row["EntrustDate"],#  委托日期
            "textField_lqqbd287": row["Sizes"],#  规格型号
            "textareaField_lqqbd290": row["Conclusion"],#  检测结论
        }

        # 发起审批流程
        process_id = yida_client.create_process_instance(
            form_uuid='FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2',
            userId=admin_user_id,
            formDataJson=formDataJson,
            process_code='TPROC--IY966L711ARIMBT6B53Z07XNO42Y3EUHE3YTLT1'
        )
        
        if process_id:
            # 更新本地数据库中的数据，设置 is_async=1
            sys_primary_key = row['SysPrimaryKey']
            update_query = f"UPDATE EntrustBills SET is_async = 1 WHERE SysPrimaryKey = '{sys_primary_key}';"
            row_count+=db.execute_update(update_query)
    response = ApiResponse.success("同步成功") if row_count > 0 else ApiResponse.fail("同步失败")
    return response    


# 检查并新增钉钉工程表单实例
# 检查并新增钉钉工程表单实例
def check_and_create_project(row,form_uuid):
    #
    data=yida_client.get_ids_by_search_field(
        form_uuid=form_uuid,
        userId=admin_user_id,
        search_field={
            "numberField_lwaeumus": [(row["ProjectNum"]),(row["ProjectNum"])]
        }
    )
    if not data:
        formDataJson = {
            "numberField_lwaeumus": int(row.get('ProjectNum')),
            "textField_logmivk3": row.get('ProjectName', ''),
            "textField_logmivk4": row.get('Area', ''),  # 无
            "textareaField_lqm1o22j": row.get('TestItemName', ''),  # 无
            "textField_logmivk6": row.get('SendSampleMan', '') if row.get('SendSampleMan', '') else row.get('EntrustName', ''),
            "textField_lqm1o22m": row.get('EntrustManPhone', ''),
            "textField_logmivk7": row.get('WitnessName', ''),
            "textField_lqm1o22n": row.get('WitnessPhone', ''),
            "textField_logmivk8": row.get('EntrustUnitName', ''),
            "textField_logmivkb": row.get('DesignUnitName', ''),
            "textField_logmivk9": row.get('WitnessUnitName', ''),
            "textField_logmivka": row.get('ConstructUnitName', '')
        }


        yida_client.create_form_data(
            form_uuid=form_uuid,
            userId=admin_user_id,
            formDataJson=formDataJson,
        )


    

#定时同步接口
@csrf_exempt
@require_http_methods(["POST"])
def sync_data(request):
    data = json.loads(request.body)

    def timestamp_to_midnight(timestamp, timezone_str='UTC'):
        if isinstance(timestamp, str):
            timestamp = int(timestamp)
        # 以UTC时区创建datetime对象
        utc_zone = pytz.timezone('UTC')
        dt = datetime.fromtimestamp(timestamp / 1000, utc_zone)
        
        # 转换到目标时区，如果服务器不是UTC时区，可以在这里调整
        target_zone = pytz.timezone(timezone_str)
        local_dt = dt.astimezone(target_zone)
        
        # 转换为午夜时间
        midnight_dt = datetime(local_dt.year, local_dt.month, local_dt.day)
        midnight_dt = target_zone.localize(midnight_dt)  # 确保时间带有时区信息
        
        return int(midnight_dt.timestamp() * 1000)

    # 在主逻辑中调用时指定时区，比如 'Asia/Shanghai'
    start_EntrustDate = data.get("startDate")
    start_EntrustDate = timestamp_to_midnight(start_EntrustDate, 'Asia/Shanghai')

    entrust_bills = []

    db = ShujianDBQueryTool()
    row_count=0
    
    # 定时同步逻辑
    query = f"""
    SELECT ReportNum, SampleNum, EntrustNum, b.Name AS TestItemName, TestItemParmName, ProjectNum, ProjectName, EntrustUnitName, SendSampleMan, EntrustDate, Sizes, Conclusion, NeedChargeMoney,SysPrimaryKey,is_async
    FROM EntrustBills a
    JOIN testitems b ON a.Code = b.Code
    WHERE a.EntrustDate = %s
    AND (a.is_async IS NULL OR a.is_async = 0)
    ORDER BY a.SysPrimaryKey 
"""
    
    params = (datetime.fromtimestamp(start_EntrustDate / 1000,pytz.timezone('Asia/Shanghai')),)
    rows = db.execute_query(query,params)
    for row in rows:
            entrust_bills.append(
            {
                "ReportNum":row[0],
                "SampleNum":row[1],
                "EntrustNum":row[2],
                "TestItemName":row[3],
                "TestItemParmName":row[4],
                "ProjectNum":row[5],
                "ProjectName":row[6],
                "EntrustUnitName":row[7],
                "SendSampleMan":row[8],
                "EntrustDate":row[9],
                "Sizes":row[10],
                "Conclusion":row[11],
                "NeedChargeMoney":row[12],
                "SysPrimaryKey":row[13],
                "is_async":row[14]
            })

    for row in entrust_bills:
        # 检查并新增钉钉工程管理表中的工程
        check_and_create_project(row=row, form_uuid="FORM-CEEA0B17FCCD4D6285FF6E7239B2D85A4R2I")

        # 分割TestItemParmName以创建多个检测参数对象
        # 先检查TestItemParmName是否为None
        if row["TestItemParmName"] is None:
            test_item_parms = []  # 如果是None，可以选择一个空列表作为默认值
        else:
            test_item_parms = row["TestItemParmName"].split('、')

        tableField_ltwahb9f = [
            {
                "selectField_ltwahb9g": item,  # 检测参数
                "numberField_ltwahb9j": row["NeedChargeMoney"]  # 单组费用
            }
            for item in test_item_parms
        ]

        # 假设 row["EntrustDate"] 是一个 datetime 对象
        entrust_date_millis = int(row["EntrustDate"].timestamp() * 1000) if row["EntrustDate"] else None

        formDataJson = {
            "selectField_ltzweoh3": "同步",  # 来源
            "textField_lqqbd279": row["EntrustNum"],  # 委托编号
            "textField_lqqbd27a": row["SampleNum"],  # 样品编号
            "textField_lqqbd27b": row["ReportNum"],  # 报告编号
            "textField_lus52pqi": row["ProjectName"],  # _工程名称
            "selectField_lrpzpf08": row["TestItemName"],  # 检测项目
            "tableField_ltwahb9f": tableField_ltwahb9f,  # 检测参数
            "textField_lt452oem": row["ProjectNum"],  # 工程编号(搜索)
            "textField_luow218y": row["ProjectNum"],  # _工程编号(搜索)
            "textField_lqqbd27k": row["EntrustUnitName"],  # 委托单位
            "textField_logmivk6": row["SendSampleMan"],  # 送样人
            "dateField_lqqbd288": entrust_date_millis,  # 委托日期
            "textField_lqqbd287": row["Sizes"],  # 规格型号
            "textareaField_lqqbd290": row["Conclusion"]  # 检测结论
        }

        # 发起审批流程
        process_id = yida_client.create_process_instance(
            form_uuid='FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2',
            userId=admin_user_id,
            formDataJson=formDataJson,
            process_code='TPROC--IY966L711ARIMBT6B53Z07XNO42Y3EUHE3YTLT1'
        )

        if process_id:
            # 更新本地数据库中的数据，设置 is_async=1
            sys_primary_key = row['SysPrimaryKey']
            update_query = f"UPDATE EntrustBills SET is_async = 1 WHERE SysPrimaryKey = '{sys_primary_key}';"
            db.execute_update(update_query)
            row_count+=1
    response = ApiResponse.success("同步成功") if row_count > 0 else ApiResponse.fail("同步失败")
    return response


# 导出建研数据
def export_hnjy_data(request):
    ProjectName = request.GET.get('ProjectName', '')
    ProjectNum = request.GET.get('ProjectNum', '')
    # 假设您从请求中获取到的时间戳是字符串形式
    timestamp_str_start = request.GET.get('EntrustDate[start]', '1046684800000')
    timestamp_str_end = request.GET.get('EntrustDate[end]', '4070908800000')

    # 将字符串转换为整数
    timestamp_int_start = int(timestamp_str_start[:10])
    timestamp_int_end = int(timestamp_str_end[:10])

    # 将时间戳转换为 datetime 对象
    start_EntrustDate = datetime.fromtimestamp(timestamp_int_start)
    end_EntrustDate = datetime.fromtimestamp(timestamp_int_end)
    # 构建查询语句，包括分页和模糊查询
    query = f"""
        SELECT ReportNum, SampleNum, EntrustNum, b.Name AS TestItemName, TestItemParmName, ProjectNum, ProjectName, EntrustUnitName, SendSampleMan, EntrustDate, Sizes, Conclusion, NeedChargeMoney
        FROM EntrustBills a
        JOIN testitems b ON a.Code = b.Code
        WHERE a.EntrustDate BETWEEN %s AND %s
        AND a.ProjectName LIKE %s
        AND a.ProjectNum LIKE %s
        ORDER BY a.SysPrimaryKey
    """
    params = (start_EntrustDate, end_EntrustDate, f"%{ProjectName}%", f"%{ProjectNum}%")
    db = ShujianDBQueryTool()
    rows = db.execute_query(query, params)
    df = pd.DataFrame(rows, columns=['报告编号', '样品编号', '委托编号', '检测项目', '检测参数', '工程编号', '工程名称', '委托单位', '委托人', '委托日期', '规格型号', '检测结论', '费用'])
    output = io.BytesIO()
    df.to_excel(output, index=False, engine='openpyxl')
    output.seek(0)
    # 创建一个 HTTP 响应，设置正确的 MIME 类型，并将文件内容作为响应体返回
    response = HttpResponse(output.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=query_results.xlsx'

    return response

@csrf_exempt
@require_http_methods(["GET"])
def view_report_pdf(request):
    report_code = request.GET.get("reportCode")
    form_inst_id = yida_client.get_ids_by_search_field("FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2",admin_user_id,{"textField_lqqbd27b":report_code})
    if not form_inst_id:
        return ApiResponse.fail("报告不存在")
    form_inst_id = form_inst_id[0]
    oss_key = f"shujian/reports/{form_inst_id}/report.pdf"
    if bucket.object_exists(oss_key):
        sign_url = bucket.sign_url('GET',oss_key, 600)
        return ApiResponse.success(sign_url)
    else:
        record = yida_client.get_form_items_by_ids("FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2",form_inst_id,admin_user_id)
        record = record[0].form_data
        if "attachmentField_lqqbd28z" in record:
            file_url = record.get("attachmentField_lqqbd28z")
        elif "attachmentField_lti99yal" in record:
            file_url = record.get("attachmentField_lti99yal")
        if not record:
            return ApiResponse.fail("报告未上传")
        file_down = json.loads(file_url)[0]["downloadUrl"]
        full_download_url = yida_client.get_full_download_url(
            user_id=admin_user_id, download_url=file_down)
        response = requests.get(full_download_url.body.result)
        if response.status_code == 200:
            file_content = response.content
            bucket.put_object(oss_key,file_content)
            sign_url = bucket.sign_url("GET",oss_key, 600)
            return ApiResponse.success(sign_url)
        return ApiResponse.fail(message="文件下载失败")

@csrf_exempt
@require_http_methods(["GET"])
def test_upload_file(request):
    """
    测试上传文件
    :param request:
    :return:
    """
    res = yida_client.update_form_data_by_id("FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2",admin_user_id,"5080ea3c-17c4-4801-9513-238bc33ae3d7",{
        "attachmentField_lti99yal":[
            {
              "downloadUrl": "https://api.zjcxjc.cn/api/v1/yida-app/common/download_file/xm_cloud/detection_render_record/20240404/11/render_20240404.pdf",
              "name": "render_20240404.pdf",
              "previewUrl": "https://api.zjcxjc.cn/api/v1/yida-app/common/download_file/xm_cloud/detection_render_record/20240404/11/render_20240404.pdf",
              "url": "https://api.zjcxjc.cn/api/v1/yida-app/common/download_file/xm_cloud/detection_render_record/20240404/11/render_20240404.pdf",
              "ext": "pdf"
            }
          ]
    })
    pass

@csrf_exempt
@require_http_methods(["POST"])
def render_model(request,model_id):
    oss_download_base_url = "https://api.zjcxjc.cn/api/v1/yida-app/common/download_file/"
    request_data = json.loads(request.body)
    render_data = request_data.get("render_data")
    form_inst_id = request_data.get("form_inst_id")
    result = xm_client.render_model(model_id,render_data)
    if(result.get('code') == 200):
        pdf_res = result.get("data")
        pdf_url = rf'{oss_download_base_url}{pdf_res["oss_path"]}'
        res = yida_client.update_form_data_by_id("FORM-5C458FC9F0944655B46924A6C5D837F2ZCN2",admin_user_id,form_inst_id,{
        "attachmentField_lum2r6b7":[
            {
              "downloadUrl": pdf_url,
              "name": "render_20240404.pdf",
              "previewUrl": pdf_url,
              "url": pdf_url,
              "ext": "pdf"
            }
          ]
        })
        if(res == "success"):
            return ApiResponse.success(pdf_url)
        else:
            return ApiResponse.fail(message=res,status=400)
    else:
        return ApiResponse.fail(message=result.get("message"),status=400)


@csrf_exempt
@require_http_methods(["GET"])
def get_hnjy_project_data(request):
    is_async = int(request.GET.get('is_async', -1))
    page_size = int(request.GET.get('pageSize', 10))
    page = int(request.GET.get('currentPage', 1))
    ProjectNum = request.GET.get('ProjectNum', '')
    ProjectName = request.GET.get('ProjectName', '')

    # 计算 OFFSET 值
    offset = (page - 1) * page_size

    # 动态构建is_async条件
    if is_async == -1:
        async_param = "(a.is_async IS NULL OR a.is_async = 1 OR a.is_async = 0)"
    elif is_async == 0:
        async_param = "(a.is_async IS NULL OR a.is_async = 0)"
    elif is_async == 1:
        async_param = "a.is_async = 1"

    # 构建查询语句，包括分页和模糊查询
    query = f"""
        SELECT a.ProjectKey, LTRIM(a.ProjectNum) AS ProjectNum, a.is_async, ProjectName, Area,
               b.Name AS EntrustName, b.Tel AS EntrustManPhone, c.Name AS WitnessName, c.Tel AS WitnessPhone,
               EntrustUnitName, WitnessUnitName, ConstructUnitName, DesignUnitName
        FROM Projects a
        LEFT JOIN ProjectsLinkMan b ON a.ProjectKey = b.ProjectKey
        LEFT JOIN ProjectsWitness c ON a.ProjectKey = c.ProjectKey
        WHERE a.ProjectNum LIKE %s AND a.ProjectName LIKE %s AND {async_param}
        ORDER BY LTRIM(a.ProjectNum)
        OFFSET %s ROWS FETCH NEXT %s ROWS ONLY
    """
    
    # 构建用于获取总记录数的查询语句
    query_total = f"""
        SELECT COUNT(*)
        FROM Projects a
        LEFT JOIN ProjectsLinkMan b ON a.ProjectKey = b.ProjectKey
        LEFT JOIN ProjectsWitness c ON a.ProjectKey = c.ProjectKey
        WHERE a.ProjectNum LIKE %s AND a.ProjectName LIKE %s AND {async_param}
    """
    params = (f"%{ProjectNum}%", f"%{ProjectName}%", offset, page_size)
    params_total = (f"%{ProjectNum}%", f"%{ProjectName}%")
    
    # 执行查询
    db = ShujianDBQueryTool()
    rows = db.execute_query(query, params)
    total = db.execute_query(query_total, params_total)[0][0]  # 总数将是第一个查询返回的第一个字段
    projects = []
    for row in rows:
        projects.append({
            "ProjectKey": row[0],
            "ProjectNum": row[1],
            "is_async": row[2],
            "ProjectName": row[3],
            "Area": row[4],
            "EntrustName": row[5],
            "EntrustManPhone": row[6],
            "WitnessName": row[7],
            "WitnessPhone": row[8],
            "EntrustUnitName": row[9],
            "WitnessUnitName": row[10],
            "ConstructUnitName": row[11],
            "DesignUnitName": row[12]
        })

    return ApiResponse.success({
        'data': projects,
        'total': total,
        "currentPage": page
    })

@csrf_exempt
@require_http_methods(["POST"])
def async_hnjy_project_data(request):
    datasource = json.loads(request.body)
    projects = datasource.get('projects', [])
    if len(projects) > 0:
        project_keys = []
        for project in projects:
            project_key = project.get('ProjectKey')
            if project_key:
                project_keys.append("'" + project_key + "'")  # 包括引号来构建SQL字符串
                check_and_create_project(project, "FORM-CEEA0B17FCCD4D6285FF6E7239B2D85A4R2I")

        if project_keys:
            # 使用join来构建适用于SQL IN子句的字符串
            param_str = ",".join(project_keys)  # 构造字符串，每个键值为单引号包围
            update = f"""
                UPDATE Projects
                SET is_async = 1
                WHERE ProjectKey IN ({param_str})
            """
            db = ShujianDBQueryTool()
            row_count = db.execute_update(update)  # 不需要额外参数，因为param_str已经在查询字符串中
            if row_count != -1:
                return ApiResponse.success("同步成功")

    return ApiResponse.fail("工程同步失败")

@csrf_exempt
@require_http_methods(["POST"])
def schedule_async_hnjy_project_data(request):


    # 构建查询语句
    query = f"""
        SELECT a.ProjectKey, LTRIM(a.ProjectNum) AS ProjectNum, a.is_async, ProjectName, Area,
               b.Name AS EntrustName, b.Tel AS EntrustManPhone, c.Name AS WitnessName, c.Tel AS WitnessPhone,
               EntrustUnitName, WitnessUnitName, ConstructUnitName, DesignUnitName
        FROM Projects a
        LEFT JOIN ProjectsLinkMan b ON a.ProjectKey = b.ProjectKey
        LEFT JOIN ProjectsWitness c ON a.ProjectKey = c.ProjectKey
        WHERE a.is_async IS NULL OR a.is_async = 0
    """ 
    # 执行查询
    db = ShujianDBQueryTool()
    rows = db.execute_query(query)
    projects = []
    for row in rows:
        projects.append({
            "ProjectKey": row[0],
            "ProjectNum": row[1],
            "is_async": row[2],
            "ProjectName": row[3],
            "Area": row[4],
            "EntrustName": row[5],
            "EntrustManPhone": row[6],
            "WitnessName": row[7],
            "WitnessPhone": row[8],
            "EntrustUnitName": row[9],
            "WitnessUnitName": row[10],
            "ConstructUnitName": row[11],
            "DesignUnitName": row[12]
        })
    
    if len(projects) > 0:
        project_keys = []
        for project in projects:
            project_key = project.get('ProjectKey')
            if project_key:
                project_keys.append("'" + project_key + "'")  # 包括引号来构建SQL字符串
                check_and_create_project(project, "FORM-CEEA0B17FCCD4D6285FF6E7239B2D85A4R2I")

        if project_keys:
            # 使用join来构建适用于SQL IN子句的字符串
            param_str = ",".join(project_keys)  # 构造字符串，每个键值为单引号包围
            update = f"""
                UPDATE Projects
                SET is_async = 1
                WHERE ProjectKey IN ({param_str})
            """
            db = ShujianDBQueryTool()
            row_count = db.execute_update(update)  # 不需要额外参数，因为param_str已经在查询字符串中
            if row_count != -1:
                return ApiResponse.success("同步成功")

    return ApiResponse.fail("工程同步失败")

@csrf_exempt
@require_http_methods(["GET"])
def testProjectAysnc(request):
    # 构建相对路径
    current_dir = os.path.dirname(os.path.abspath(__file__))
    excel_file_path = os.path.join(current_dir, "excel_file.xlsx")
    
    # 读取Excel文件
    df = pd.read_excel(excel_file_path)
    
    # 获取工程编号列表
    project_numbers = df['工程编号'].tolist()
    
    if project_numbers:
        # 构建工程编号字符串，用于SQL NOT IN子句
        project_num_str = ",".join([f"'{num}'" for num in project_numbers])
        
        # 构建更新SQL语句
        update_query = f"""
            UPDATE Projects
            SET is_async = 0
            WHERE ProjectNum NOT IN ({project_num_str})
        """
        #return ApiResponse.success(update_query)
        
        # 执行更新查询
        db = ShujianDBQueryTool()
        row_count = db.execute_update(update_query)
        
        if row_count != -1:
            return ApiResponse.success(f"{row_count} 条记录更新成功")
    
    return ApiResponse.fail("没有需要更新的工程编号")
